import pandas
import pandas as pd
from openpyxl.reader.excel import load_workbook


# 根据列名把这一列数据移动到第一列
def move_column(dataframe, move_column_name):
    # 获取newYearListTable2所有列
    cols = list(dataframe.columns)
    # 将column_to_move列移动到第一列
    cols.insert(0, cols.pop(cols.index(move_column_name)))
    newTable2AfterMoveColumn = dataframe[cols]
    return newTable2AfterMoveColumn

#把数据保存到Excel文件中最后一个sheet中
def save_to_excel(dataframe, url):
    # mode = 'a'表示以追加模式打开文件。
    # if_sheet_exists = 'overlay'表示如果 sheet已经存在，则覆盖它。这里我们确保不会覆盖已有的 sheet
    with pd.ExcelWriter(url, mode='a', if_sheet_exists='overlay') as writer:
        # 获取当前工作簿的所有 sheet 名称
        sheet_names = writer.book.sheetnames
        # 计算新的 sheet 名称
        new_sheet_name = f'Sheet{len(sheet_names) + 1}'
        # 将数据写入新的 sheet
        dataframe.to_excel(writer, sheet_name=new_sheet_name, index=False)
    return new_sheet_name
def get_base_list(url):
    url = fr"{url}"
    #获取Excel文件第一个sheet表数据--电子监管号---实际管理区
    #3601002018B00086    青云谱区
    elecSuperNum2ActualManageAreaTable = pd.read_excel(rf'{url}')  # 'r'是转义字符，避免路径中的'\'被转译
    #print(elecSuperNum2ActualManageAreaTable)

    #获取Excel文件第二个sheet表数据--实际管理区---设区市
    #婺源县    上饶市
    actualManageArea2CityTable = pd.read_excel(rf'{url}', sheet_name=1)
    #print(actualManageArea2SetCountyTable)

    # 基数清单
    baseListTable = pd.read_excel(rf'{url}', sheet_name=3)
    #print(baseListTable)

    #电子监管号匹配实际管理区
    baseListTable2 = pd.merge( baseListTable,elecSuperNum2ActualManageAreaTable,left_on='电子监管号',right_on='电子监管号',how='left')
    #print(baseListTable2)
    column_to_move = '实际管理区'
    #获取baseListTable2所有列
    cols = list(baseListTable2.columns)
    #将column_to_move列移动到第一列
    cols.insert(0, cols.pop(cols.index(column_to_move)))
    baseListTable2AfterMoveColumn = baseListTable2[cols]

    #实际管理区匹配设区市
    baseListTable3 =pd.merge( baseListTable2AfterMoveColumn,actualManageArea2CityTable,left_on='实际管理区',right_on='实际管理区',how='left')
    column_to_move = '设区市'
    #获取baseListTable3所有列
    cols = list(baseListTable3.columns)
    #将column_to_move列移动到第一列
    cols.insert(0, cols.pop(cols.index(column_to_move)))
    baseListTable3AfterMoveColumn = baseListTable3[cols]

    #提取约定（变更）动工时间和实际动工时间---变成规范时间格式
    baseListTable3AfterMoveColumn['约定（变更）动工时间'] = pd.to_datetime(baseListTable3AfterMoveColumn['约定（变更）动工时间'])
    baseListTable3AfterMoveColumn['约定（变更）动工时间'] = baseListTable3AfterMoveColumn['约定（变更）动工时间'].dt.strftime('%Y-%m-%d')

    #实际动工时间--变成规范时间格式
    baseListTable3AfterMoveColumn['实际动工时间'] = pd.to_datetime(baseListTable3AfterMoveColumn['实际动工时间'])
    baseListTable3AfterMoveColumn['实际动工时间'] = baseListTable3AfterMoveColumn['实际动工时间'].dt.strftime('%Y-%m-%d')

    # 实际竣工时间--变成规范时间格式
    baseListTable3AfterMoveColumn['实际竣工时间'] = pd.to_datetime(baseListTable3AfterMoveColumn['实际竣工时间'])
    baseListTable3AfterMoveColumn['实际竣工时间'] = baseListTable3AfterMoveColumn['实际竣工时间'].dt.strftime('%Y-%m-%d')

    #保存数据到Excel文件
    save_to_excel(baseListTable3AfterMoveColumn,url)
    #返回处理后的数据
    return baseListTable3AfterMoveColumn

def get_handled_list(url):
    url = fr"{url}"
    # 获取Excel文件第一个sheet表数据--电子监管号---实际管理区
    # 3601002018B00086    青云谱区
    elecSuperNum2ActualManageAreaTable = pd.read_excel(rf'{url}')  # 'r'是转义字符，避免路径中的'\'被转译
    # print(elecSuperNum2ActualManageAreaTable)

    # 获取Excel文件第二个sheet表数据--实际管理区---设区市
    # 婺源县    上饶市
    actualManageArea2CityTable = pd.read_excel(rf'{url}', sheet_name=1)
    # print(actualManageArea2SetCountyTable)

    # 已处置清单表
    handledListTable = pd.read_excel(rf'{url}', sheet_name=4)

    # 电子监管号匹配实际管理区
    handledListTable2 = pd.merge(handledListTable, elecSuperNum2ActualManageAreaTable, left_on='电子监管号', right_on='电子监管号', how='left')
    #print(handledListTable2)
    column_to_move = '实际管理区'
    # 获取handledListTable2所有列
    cols = list(handledListTable2.columns)
    # 将column_to_move列移动到第一列
    cols.insert(0, cols.pop(cols.index(column_to_move)))
    handledListTable2AfterMoveColumn = handledListTable2[cols]

    # 实际管理区匹配设区市
    handledListTable3 = pd.merge(handledListTable2AfterMoveColumn, actualManageArea2CityTable, left_on='实际管理区',right_on='实际管理区', how='left')
    column_to_move = '设区市'
    # 获取baseListTable3所有列
    cols = list(handledListTable3.columns)
    # 将column_to_move列移动到第一列
    cols.insert(0, cols.pop(cols.index(column_to_move)))
    handledListTable3AfterMoveColumn = handledListTable3[cols]

    # 提取约定（变更）动工时间和实际动工时间---变成规范时间格式
    handledListTable3AfterMoveColumn['约定（变更）动工时间'] = pd.to_datetime(handledListTable3AfterMoveColumn['约定（变更）动工时间'])
    handledListTable3AfterMoveColumn['约定（变更）动工时间'] = handledListTable3AfterMoveColumn['约定（变更）动工时间'].dt.strftime('%Y-%m-%d')

    # 实际动工时间--变成规范时间格式
    handledListTable3AfterMoveColumn['实际动工时间'] = pd.to_datetime(handledListTable3AfterMoveColumn['实际动工时间'])
    handledListTable3AfterMoveColumn['实际动工时间'] = handledListTable3AfterMoveColumn['实际动工时间'].dt.strftime('%Y-%m-%d')

    # 实际动工时间--变成规范时间格式
    handledListTable3AfterMoveColumn['实际竣工时间'] = pd.to_datetime(handledListTable3AfterMoveColumn['实际竣工时间'])
    handledListTable3AfterMoveColumn['实际竣工时间'] = handledListTable3AfterMoveColumn['实际竣工时间'].dt.strftime('%Y-%m-%d')

    # 保存数据到Excel文件
    save_to_excel(handledListTable3AfterMoveColumn, url)
    # 返回处理后的数据
    return handledListTable3AfterMoveColumn
def get_newadd_inyear_list(url):
    url = fr"{url}"
    # 获取Excel文件第一个sheet表数据--电子监管号---实际管理区
    # 3601002018B00086    青云谱区
    elecSuperNum2ActualManageAreaTable = pd.read_excel(rf'{url}')  # 'r'是转义字符，避免路径中的'\'被转译
    #print(elecSuperNum2ActualManageAreaTable)

    # 获取Excel文件第二个sheet表数据--实际管理区---设区市
    # 婺源县    上饶市
    actualManageArea2CityTable = pd.read_excel(rf'{url}', sheet_name=1)
    #print(actualManageArea2SetCountyTable)

    # 获取Excel文件第三个sheet表数据--县区编号（电子监管号前6位）---县区名称----地级市
    # 361130    婺源县    上饶市
    countyNum2countyName2CityTable = pd.read_excel(rf'{url}', sheet_name=2)
    countyNum2countyName2CityTable.columns.values[0] = 'col1'
    countyNum2countyName2CityTable.columns.values[1] = 'countyNum'
    countyNum2countyName2CityTable.columns.values[2] = 'countyName'
    countyNum2countyName2CityTable.columns.values[3] = 'cityName'
    countyNum2countyName2CityTable.columns.values[4] = 'col5'
    countyNum2countyName2CityTable.columns.values[5] = 'col6'
    countyNum2countyName2CityTable.columns.values[6] = 'col7'
    countyNum2countyName2CityTable['countyNum'] = countyNum2countyName2CityTable['countyNum'].astype(str).str.slice(0, 6)
    #print(countyNum2countyName2CityTable)

    # 年内新增清单
    newYearListTable = pd.read_excel(rf'{url}', sheet_name=5)
    #print(newYearListTable)

    # 提取电子监管号的前6位
    newYearListTable['countyNum'] = newYearListTable['电子监管号'].str.slice(0, 6)
    #print(newYearListTable)
    newYearListTable2 = move_column(newYearListTable, 'countyNum')
    #print(newYearListTable2)
    # 合并两个表
    merged_table = pd.merge(newYearListTable2, countyNum2countyName2CityTable, on='countyNum', how='left')
    merged_table = merged_table.drop(columns=['col1'])
    merged_table = merged_table.drop(columns=['col5'])
    merged_table = merged_table.drop(columns=['col6'])
    merged_table = merged_table.drop(columns=['col7'])
    merged_table = move_column(merged_table, 'countyName')
    merged_table = move_column(merged_table, 'cityName')

    merged_table['签订日期'] = pd.to_datetime(merged_table['签订日期'])
    merged_table['签订日期'] = merged_table['签订日期'].dt.strftime('%Y-%m-%d')

    merged_table['约定动工时间'] = pd.to_datetime(merged_table['约定动工时间'])
    merged_table['约定动工时间'] = merged_table['约定动工时间'].dt.strftime('%Y-%m-%d')

    merged_table['闲置产生时间'] = pd.to_datetime(merged_table['闲置产生时间'])
    merged_table['闲置产生时间'] = merged_table['闲置产生时间'].dt.strftime('%Y-%m-%d')

    merged_table['闲置处置时间'] = pd.to_datetime(merged_table['闲置处置时间'])
    merged_table['闲置处置时间'] = merged_table['闲置处置时间'].dt.strftime('%Y-%m-%d')
    #print(merged_table)

    save_to_excel(merged_table, url)
    #merged_table.to_excel(output_file, index=False, sheet_name='Sheet1')
    return merged_table
def sum_by_column(dataframe, column_names4group,column_name4value):
    # 分类统计
    # 按设区市分组并计算项目数量
    grouped_sum = dataframe.groupby(column_names4group)[column_name4value].sum().reset_index()
    print(grouped_sum)
    # 计算每个 cityName 的小计
    subgrouped_sum = grouped_sum.groupby(column_names4group[0])[column_name4value].sum().reset_index()
    subgrouped_sum[column_names4group[1]] = subgrouped_sum[column_names4group[0]].apply(lambda x: f'{x} (小计)')
    print(subgrouped_sum)
    # 将原始数据和小计数据合并
    result = pd.concat([grouped_sum, subgrouped_sum], ignore_index=True).sort_values(
        by=column_names4group)
    # 输出结果
    print(result)
    return result
def size_by_column(dataframe, column_names4group):
    # 分类统计
    # 按设区市分组并计算项目数量
    grouped_size = dataframe.groupby(column_names4group).size().reset_index(name='count')
    print(grouped_size)
    # 计算每个 cityName 的小计
    subgrouped_size = dataframe.groupby(column_names4group[0]).size().reset_index(name='count')
    subgrouped_size[column_names4group[1]] = subgrouped_size[column_names4group[0]].apply(lambda x: f'{x} (小计)')
    print(subgrouped_size)
    # 将原始数据和小计数据合并
    result = pd.concat([grouped_size, subgrouped_size], ignore_index=True).sort_values(
        by=column_names4group)
    # 输出结果
    print(result)
    return result
def merge_same_column(url,sheetName):
    # 加载Excel文件
    wb = load_workbook(url)
    # 选择要处理的sheet
    ws = wb[sheetName]
    print(ws)
    # 获取A列的数据
    column_data = [cell.value for cell in ws['A']]
    start_row = 1
    # 遍历A列的数据，判断是否与上一行相同，不同则合并
    for i in range(1, len(column_data)):
        if column_data[i] != column_data[i - 1]:
            if i - start_row > 0:
                ws.merge_cells(start_row=start_row, end_row=i, start_column=1, end_column=1)
            start_row = i + 1

    # 处理最后一段相同值的单元格
    if len(column_data) - start_row > 0:
        ws.merge_cells(start_row=start_row, end_row=len(column_data), start_column=1, end_column=1)
    # 保存修改后的 Excel 文件
    wb.save(url)

if __name__ == '__main__':
    # resultTable = get_base_list(r'E:\资料\省自然资源厅\1.example.xlsx')
    # print(resultTable)
    # resultTable = get_handled_list(r'E:\资料\省自然资源厅\1.example.xlsx')
    # print(resultTable)
    resultTable = get_newadd_inyear_list(r'E:\资料\省自然资源厅\1.example.xlsx')
    result1 = sum_by_column(resultTable, ['cityName', 'countyName'], '供应面积')
    result2 = size_by_column(resultTable, ['cityName', 'countyName'])
    result3 = pd.merge(result1, result2, on=['cityName', 'countyName'], how='outer')
    result3.sort_values(['cityName', 'countyName'])
    print(result3)
    save_sheet_name = save_to_excel(result3, r'E:\资料\省自然资源厅\1.example.xlsx')
    merge_same_column(r'E:\资料\省自然资源厅\1.example.xlsx',save_sheet_name)
    # # 分类统计
    # # 按设区市分组并计算项目数量
    # city_county_grouped_sum = resultTable.groupby(['cityName','countyName'])['供应面积'].sum().reset_index()
    # print(city_county_grouped_sum)
    # # 计算每个 cityName 的小计
    # city_subgrouped_sum = city_county_grouped_sum.groupby('cityName')['供应面积'].sum().reset_index()
    # city_subgrouped_sum['countyName'] = city_subgrouped_sum['cityName'].apply(lambda x: f'{x} (小计)')
    # print(city_subgrouped_sum)
    # # 将原始数据和小计数据合并
    # result = pd.concat([city_county_grouped_sum, city_subgrouped_sum], ignore_index=True).sort_values(by=['cityName', 'countyName'])
    # # 输出结果
    # print(result)

    # city_project_count = resultTable.groupby(['cityName','countyName'])['供应面积'].sum().reset_index()
    # print(city_project_count)
    #
    # # 按县区名称分组并计算项目数量
    # county_project_count = resultTable.groupby('countyName').size()
    # print(county_project_count)
    #
    # # 按设区市和县区名称分组并计算项目数量
    # city_county_project_count = resultTable.groupby(['cityName', 'countyName']).size().unstack(fill_value=0)
    # print(city_county_project_count)
